package com.spring.demo.core.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.spring.demo.core.dao.IEmployeeDao;
import com.spring.demo.core.entity.Employee;

@Repository
public class EmployeeDaoJdbc implements IEmployeeDao {

	@Autowired
	@Qualifier("jdbcDataSource")
	private DataSource dataSource;
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	private static final String EMPLOYEE_INSERT_SQL = "insert into EMPLOYEE(age, name) values(?, ?)";
	private static final String EMPLOYEE_SELECT_SQL = "select id, age, name from employee where id=?";

	@Override
	public void insert(Employee employee) {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement statement = conn.prepareStatement(
					EMPLOYEE_INSERT_SQL, Statement.RETURN_GENERATED_KEYS);
			statement.setInt(1, employee.getAge());
			statement.setString(2, employee.getName());
			statement.executeUpdate();
			ResultSet rs = statement.getGeneratedKeys();
			if (rs.next()) {
				long id = rs.getLong(1);
				employee.setId(id);
			}
			statement.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	@Override
	public Employee select(Long id) {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(EMPLOYEE_SELECT_SQL);
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				Employee employee = new Employee();
				employee.setId(rs.getLong(1));
				employee.setAge(rs.getInt(2));
				employee.setName(rs.getString(3));
				return employee;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	@Override
	public void save(final Employee employee) {

		jdbcTemplate.update(EMPLOYEE_INSERT_SQL, employee.getAge(),
				employee.getName());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		PreparedStatementCreator psc = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(
						EMPLOYEE_INSERT_SQL, Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, employee.getAge());
				ps.setString(2, employee.getName());
				return ps;
			}
		};
		jdbcTemplate.update(psc, keyHolder);
		employee.setId(keyHolder.getKey().longValue());
	}

	@Override
	public Employee findById(Long id) {
		return jdbcTemplate.queryForObject(EMPLOYEE_SELECT_SQL,
				new Object[] { id }, new EmployeeRowMapper());
	}

	/**
	 * employee mapper
	 * 
	 * @author sean
	 * 
	 */
	private class EmployeeRowMapper implements RowMapper<Employee> {
		@Override
		public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
			Employee employee = new Employee();
			employee.setId(rs.getLong("id"));
			employee.setAge(rs.getInt("age"));
			employee.setName(rs.getString("name"));
			return employee;
		}
	}
}
